<?php
if (!defined("BASEPATH")) exit("No direct script access allowed");

class Message extends MY_Model {

	function __construct() {
		parent::__construct();
		$this->tableName = "message";
		$this->prefix = "mes";
	}

	function getUnreadMessages($where) {
		$qs =	"
				select
					mes_id, mes_sender_usr_id, mes_reader_usr_id, mes_ins_id, mes_unique_id, mes_subject, mes_message, mes_createdate, mes_lastupd, mes_file_name, mes_file_name_original, mes_readdate
					, sender.usr_del as sender_usr_del, sender.usr_email as sender_usr_email, coalesce(sendercat.e2c_text, sendercat.e2c_int) as sender_fullname, sendergrp.u2g_id as sendergrp_u2g_id
					, reader.usr_del as reader_usr_del, reader.usr_email as reader_usr_email, coalesce(readercat.e2c_text, readercat.e2c_int) as reader_fullname, readergrp.u2g_id as readergrp_u2g_id
					, ins_name, ins_url_name
				from
					".$this->db->dbprefix.$this->tableName."
					join ".$this->db->dbprefix."institute on ins_id = mes_ins_id and ins_del = 0
					
					left join ".$this->db->dbprefix."user as sender on sender.usr_id = mes_sender_usr_id
					left join ".$this->db->dbprefix."usr2grp as sendergrp on sendergrp.u2g_usr_id = mes_sender_usr_id and sendergrp.u2g_del = 0 and sendergrp.u2g_grp_id = 2
					left join ".$this->db->dbprefix."ent2cat as sendercat on sender.usr_id = sendercat.e2c_ent_id and sendercat.e2c_del = 0 and sendercat.e2c_cat_id = '".intval($this->config->item("categoryUSRFullname"))."'
					
					left join ".$this->db->dbprefix."user as reader on reader.usr_id = mes_reader_usr_id
					left join ".$this->db->dbprefix."usr2grp as readergrp on readergrp.u2g_usr_id = mes_reader_usr_id and readergrp.u2g_del = 0 and readergrp.u2g_grp_id = 2
					left join ".$this->db->dbprefix."ent2cat as readercat on reader.usr_id = readercat.e2c_ent_id and readercat.e2c_del = 0 and readercat.e2c_cat_id = '".intval($this->config->item("categoryUSRFullname"))."'
				where
					mes_del = 0 and mes_reader_usr_id is null ".(is_null($where) ? "" : " and ".$where)."
				order by
					mes_createdate DESC
				";
		$result = $this->db->query($qs);

		$getUnreadMessages = array();
		if($result->num_rows()) {
			foreach ($result->result() as $row) {

				$getUnreadMessages[] = array(
					"mesUniqueId" => $row->mes_unique_id,
					"mesMessage" => $row->mes_message,
					"mesCreatedate" => str_replace("-", ".", $row->mes_createdate),
					"senderUsrEmail" => $row->sender_usr_email,
				);

			}
		}

		return $getUnreadMessages;
	}

	function getUnreadMessagesCnt($where = NULL) {
		$qs =	"
				select
					count(*) as cnt
				from
					".$this->db->dbprefix.$this->tableName."
				where
					mes_del = 0 and mes_reader_usr_id is null ".((!is_null($where))?(" and ".$where):(""))."
				";
		$result = $this->db->query($qs);

		if($result->num_rows()) {
			foreach ($result->result() as $row) {
				return $row->cnt;
			}
		}
	}

	function getMessage($where = NULL) {

		$qs =	"
				select
					mes_unique_id
				from
					".$this->db->dbprefix.$this->tableName."
				where
					mes_del = 0 ".((!is_null($where))?(" and ".$where):(""))."
				group by
					mes_unique_id
				";
		$result = $this->db->query($qs)->result_array();
		$mes_unique_ids = "";
		foreach($result as $mui) {
			$mes_unique_ids .= (($mes_unique_ids)?(","):("")) . $mui["mes_unique_id"];
		}
		if(!$mes_unique_ids) return array();
	
		$qs =	"
				select
					mes_id, mes_sender_usr_id, mes_reader_usr_id, mes_ins_id, mes_unique_id, mes_subject, mes_message, mes_createdate, mes_lastupd, mes_file_name, mes_file_name_original, mes_readdate
					, sender.usr_del as sender_usr_del, sender.usr_email as sender_usr_email, coalesce(sendercat.e2c_text, sendercat.e2c_int) as sender_fullname, sendergrp.u2g_id as sendergrp_u2g_id
					, reader.usr_del as reader_usr_del, reader.usr_email as reader_usr_email, coalesce(readercat.e2c_text, readercat.e2c_int) as reader_fullname, readergrp.u2g_id as readergrp_u2g_id
					, ins_name, ins_url_name
				from
					".$this->db->dbprefix.$this->tableName."
					join ".$this->db->dbprefix."institute on ins_id = mes_ins_id and ins_del = 0
					
					left join ".$this->db->dbprefix."user as sender on sender.usr_id = mes_sender_usr_id
					left join ".$this->db->dbprefix."usr2grp as sendergrp on sendergrp.u2g_usr_id = mes_sender_usr_id and sendergrp.u2g_del = 0 and sendergrp.u2g_grp_id = 2
					left join ".$this->db->dbprefix."ent2cat as sendercat on sender.usr_id = sendercat.e2c_ent_id and sendercat.e2c_del = 0 and sendercat.e2c_cat_id = '".intval($this->config->item("categoryUSRFullname"))."'
					
					left join ".$this->db->dbprefix."user as reader on reader.usr_id = mes_reader_usr_id
					left join ".$this->db->dbprefix."usr2grp as readergrp on readergrp.u2g_usr_id = mes_reader_usr_id and readergrp.u2g_del = 0 and readergrp.u2g_grp_id = 2
					left join ".$this->db->dbprefix."ent2cat as readercat on reader.usr_id = readercat.e2c_ent_id and readercat.e2c_del = 0 and readercat.e2c_cat_id = '".intval($this->config->item("categoryUSRFullname"))."'
				where
					mes_del = 0 and mes_unique_id in (".$mes_unique_ids.")
				order by
					mes_createdate DESC
				";
		$result = $this->db->query($qs);
		//Common::pr($this->db->last_query());

		$getMessage = array();
		if($result->num_rows()) {
			foreach ($result->result() as $row) {

				$getMessage[$row->mes_unique_id][] = array(
					"mesId" => $row->mes_id,
					"mesUniqueId" => $row->mes_unique_id,
					"mesSenderUsrId" => $row->mes_sender_usr_id,
					"mesInsId" => $row->mes_ins_id,
					"mesReaderUsrId" => $row->mes_reader_usr_id,
					"mesSubject" => $row->mes_subject,
					"mesMessage" => $row->mes_message,
					"mesCreatedate" => str_replace("-", ".", $row->mes_createdate),
					"mesReaddate" => str_replace("-", ".", $row->mes_readdate),
					"mesReaddateRaw" => $row->mes_readdate,
					"mesLastupd" => str_replace("-", ".", $row->mes_lastupd),
					"mesFileName" => $row->mes_file_name,
					"mesFileNameOriginal" => $row->mes_file_name_original,
					"senderUsrDel" => $row->sender_usr_del,
					"senderUsrEmail" => $row->sender_usr_email,
					"readerUsrDel" => $row->reader_usr_del,
					"readerUsrEmail" => $row->reader_usr_email,
					"insName" => $row->ins_name,
					"insUrlName" => $row->ins_url_name,
					"senderFullname" => $row->sender_fullname,
					"senderIsIns" => !is_null($row->sendergrp_u2g_id),
					"readerFullname" => $row->reader_fullname,
					"readerIsIns" => !is_null($row->readergrp_u2g_id),
				);

			}
		}

		return $getMessage;
	}


	function getUniqueId($where = NULL) {
		$cnt = 1;
		while($cnt) {
			$rand = mt_rand(0,2147483647);
			$qs =	"
						select
							count(*) as cnt
						from
							".$this->db->dbprefix.$this->tableName."
						where
							mes_del = 0 and mes_unique_id = '".$this->db->escape_str($rand)."'
						";
			//$cnt = $this->db->query($qs)->result()[0]->cnt;
			$result = $this->db->query($qs)->result();
			$cnt = $result[0]->cnt;
		}
		return $rand;
	}
	
	function getFileNameOriginalByFileName($where = NULL) {
		$qs =	"
					select
						mes_file_name_original
					from
						".$this->db->dbprefix.$this->tableName."
					where
						mes_del = 0 ".((!is_null($where))?(" and ".$where):(""))."
					";
					//Common::pr($qs);
		$result = $this->db->query($qs);
		if($result->num_rows()) {
			$result = $result->result();
			return $result[0]->mes_file_name_original;
		}
		return FALSE;
	}

	function checkMesId($where = NULL) {
		if(Common::isSuperAdmin()) return TRUE;
		$qs =	"
					select
						count(*) as cnt
					from
						".$this->db->dbprefix.$this->tableName."
					where
						mes_del = 0 ".((!is_null($where))?(" and ".$where):(""))."
					";
		$result = $this->db->query($qs)->result();
		return $result[0]->cnt;
	}

	function getFirstSender($mes_unique_id) {
		// del flag nem kell figyelni message esetben itt
		$qs =	"
					select
						usr_email
					from
						".$this->db->dbprefix.$this->tableName."
						left join ".$this->db->dbprefix."user on usr_id = mes_sender_usr_id and usr_del = 0
					where
						mes_unique_id = '".$this->db->escape_str($mes_unique_id)."'
					order by
						mes_id asc
					limit 1
					";
		$result = $this->db->query($qs)->result();
		if(count($result)) {
			return $result[0]->usr_email;
		}
		return FALSE;
	}
	
}